package edu.hhu.chart.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.print.DocFlavor.STRING;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import edu.hhu.chart.domain.EchartLineData;
import edu.hhu.chart.domain.EchartShow;
import edu.hhu.chart.domain.Series;
import edu.hhu.chart.domain.itemStyle;
import edu.hhu.chart.domain.bar.EchartBarData;
import edu.hhu.chart.domain.bar.EchartBarSeries;
import edu.hhu.chart.domain.bar.Legend;
import edu.hhu.chart.domain.bar.XAxis;
import edu.hhu.chart.domain.pie.EchartPieData;
import edu.hhu.chart.domain.pie.EchartPieSeries;
import edu.hhu.chart.domain.pie.PieData;

public class ChartDao {

	/**
	 * 获取柱状图数据
	 * 
	 * @return
	 */
	public List<EchartShow> getBarChartData() {

		List<EchartShow> echartShows = new ArrayList<EchartShow>();
		EchartBarData echartBarData = new EchartBarData();
		List<EchartBarSeries> series = new ArrayList<EchartBarSeries>();
		List<String> legendlist = new ArrayList<String>();// 数据分组
		List<String> xAxislist = new ArrayList<String>();// 横坐标
		String showname = "延川南生产井情况";
		List<Record> data;
		// Map<String, Integer> wellType = new HashMap<>();
		List<BigDecimal> QQ = new ArrayList<>();
		List<BigDecimal> AVGQQ = new ArrayList<>();

		String sql = "SELECT t1.`wellname`,t2.`wellgroupcode` FROM cjkj_tree t1,(SELECT t.`wellgroupcode` FROM cjkj t GROUP BY t.`wellgroupcode`) AS t2 "
				+ "WHERE t1.`wellcode` = t2.`wellgroupcode` ";
		// System.out.println(sql);
		data = Db.use("mysql").find(sql);
		for (Record record : data) {
			xAxislist.add(record.getStr("wellname"));
			String wellgroupcode = record.getStr("wellgroupcode");

			String QQSql = "SELECT CONVERT(t.`QQ`,DECIMAL(10,2)) AS QQ FROM  jzhz t WHERE t.`wellgroupcode` = '"
					+ wellgroupcode + "' AND t.`QQ` IS NOT NULL ORDER BY t.`SCRQ` DESC";

			data = Db.use("mysql").find(QQSql);
			BigDecimal QQDecimal = data.get(0).getBigDecimal("QQ");
			if (QQDecimal == null) {
				QQDecimal = new BigDecimal("0.0");
			}
			QQ.add(QQDecimal);

			String AVGQQSql = "SELECT CONVERT(SUM(t.`QQ`)/COUNT(*),DECIMAL(10,2)) AS AVGQQ FROM  jzhz t WHERE t.`wellgroupcode` = '"
					+ wellgroupcode + "' AND t.`QQ` IS NOT NULL";

			data = Db.use("mysql").find(AVGQQSql);
			BigDecimal AVGQQDecimal = data.get(0).getBigDecimal("AVGQQ");
			if (AVGQQDecimal == null) {
				AVGQQDecimal = new BigDecimal("0.0");
			}
			AVGQQ.add(AVGQQDecimal);

			// wellType.put(record.getStr("wellgroupcode"), 0);
		}
		legendlist.add("最新日产量");
		legendlist.add("平均日产量");

		EchartBarSeries echartBarSeries1 = new EchartBarSeries("最新日产量", "bar", QQ);
		EchartBarSeries echartBarSeries2 = new EchartBarSeries("平均日产量", "bar", AVGQQ);

		series.add(echartBarSeries1);
		series.add(echartBarSeries2);

		/*
		 * sql =
		 * "SELECT tt.jch FROM jtjcsj tt WHERE TRIM(tt.jch)  != '' GROUP BY tt.jch"
		 * ; // System.out.println(sql); data = Db.use("mysql").find(sql); for
		 * (Record record : data) {
		 * 
		 * String jch = record.getStr("jch"); legendlist.add(jch);
		 * 
		 * sql = " SELECT wellgroupcode,COUNT(*) as num FROM " +
		 * " ( SELECT t1.`wellcode`,t1.`JCH`,t2.`wellgroupcode` FROM jtjcsj t1,cjkj t2 WHERE t1.`wellcode` = t2.`wellcode`)"
		 * + " t WHERE t.JCH='" + jch + "' GROUP BY t.wellgroupcode"; //
		 * System.out.println(sql);
		 * 
		 * data = Db.use("mysql").find(sql); for (Record record2 : data) {
		 * String wellgroupcode = record2.getStr("wellgroupcode"); int num =
		 * record2.getNumber("num").intValue(); //
		 * System.out.println("num--------------"+num);
		 * wellType.put(wellgroupcode, num); } // 插入到前台柱状图Bean中 List<Integer>
		 * counts = new ArrayList<>();
		 * 
		 * Integer sum = 0; for (Map.Entry<String, Integer> entry :
		 * wellType.entrySet()) { sum += entry.getValue();
		 * counts.add(entry.getValue()); entry.setValue(0); } counts.add(sum);
		 * 
		 * EchartBarSeries echartBarSeries = new EchartBarSeries();
		 * echartBarSeries.setName(jch); echartBarSeries.setType("bar");
		 * echartBarSeries.setData(counts); series.add(echartBarSeries); }
		 * xAxislist.add("合计");
		 */

		echartBarData.setLegend(new Legend(legendlist));
		echartBarData.setxAxis(new XAxis(xAxislist));
		echartBarData.setSeries(series);
		echartBarData.setShowName(showname);
		echartShows.add(new EchartShow("chartContainer2", echartBarData));

		return echartShows;
	}

	/**
	 * 获取饼状图数据
	 * 
	 * @return
	 */
	public List<EchartShow> getPieChartData() {

		List<EchartShow> echartShows = new ArrayList<EchartShow>();
		EchartPieData echartPieData = new EchartPieData();
		EchartPieSeries echartPieSeries = new EchartPieSeries();

		List<String> center = new ArrayList<>();
		List<PieData> data = new ArrayList<>();

		center.add("50%");
		center.add("60%");

		Map<String, String> sqlMap = new HashMap<>();

		sqlMap.put(">1000 m3/d",
				"SELECT COUNT(*) AS NUM FROM (SELECT t.`wellcode`,t.`wellname`,t.`QQ`,MAX(t.`SCRQ`) AS SCRQ FROM pcdtsjtrue t WHERE t.`QQ` IS NOT NULL GROUP BY t.`wellcode`) AS tt WHERE tt.`QQ` > 1000");
		sqlMap.put("500-1000 m3/d",
				"SELECT COUNT(*) AS NUM FROM (SELECT t.`wellcode`,t.`wellname`,t.`QQ`,MAX(t.`SCRQ`) AS SCRQ FROM pcdtsjtrue t WHERE t.`QQ` IS NOT NULL GROUP BY t.`wellcode`) AS tt WHERE tt.`QQ` BETWEEN 500 AND 1000");
		sqlMap.put("<500 m3/d",
				"SELECT COUNT(*) AS NUM FROM (SELECT t.`wellcode`,t.`wellname`,t.`QQ`,MAX(t.`SCRQ`) AS SCRQ FROM pcdtsjtrue t WHERE t.`QQ` IS NOT NULL GROUP BY t.`wellcode`) AS tt WHERE tt.`QQ` < 500");

		List<String> legendData = new ArrayList<>();
		for (Map.Entry<String, String> entry : sqlMap.entrySet()) {
			int number = Db.use("mysql").queryLong(entry.getValue()).intValue();
			PieData pieData = new PieData(number, entry.getKey());
			legendData.add(entry.getKey());
			data.add(pieData);
		}

		echartPieSeries.setName("容量");
		echartPieSeries.setType("pie");
		echartPieSeries.setRadius("55%");
		echartPieSeries.setCenter(center);
		echartPieSeries.setData(data);
		echartPieSeries.setClockWise(true);
		echartPieSeries.setStartAngle(20);
		echartPieSeries.setMinAngle(10);

		echartPieData.setData(legendData);
		echartPieData.setSeries(echartPieSeries);

		echartShows.add(new EchartShow("chartContainer1", echartPieData));

		return echartShows;
	}

	/**
	 * 获取双Y轴图显示数据
	 * 
	 * @param fields
	 *            要显示的参数字段，其个数即要生成的图的个数
	 * @return
	 */
	public List<EchartShow> getDoubleLineChartData(List<String> fields) {

		List<EchartShow> echartShows = new ArrayList<EchartShow>();

		String[][] yAxis1 = { { "QQ", "QL" }, { "日产气(方/天)", "日产液(方/天)" } };
		String sql = "SELECT t.SCRQ,SUM(t.`QQ`) AS QQ,SUM(t.`QL`) AS QL FROM pcdtsjtrue t GROUP BY t.SCRQ ORDER BY t.SCRQ";
		EchartLineData echartdata1 = getDoubleLineData(sql, yAxis1, "延川南产气井日产气/日产液情况");

		String[][] yAxis2 = { { "AQQ", "APWF" }, { "平均日产气(方/天)", "井底流压(MPA)" } };
		sql = "SELECT t.SCRQ,SUM(t.`QQ`)/COUNT(DISTINCT t.`wellname`) AS AQQ,SUM(t.`PWF`)/COUNT(DISTINCT t.`wellname`) AS APWF FROM pcdtsjtrue t GROUP BY t.SCRQ ORDER BY t.SCRQ;";
		EchartLineData echartdata2 = getDoubleLineData(sql, yAxis2, "延川南产气井平均日产气/平均井底流压情况");

		echartShows.add(new EchartShow("doulbeLine1", echartdata1));
		echartShows.add(new EchartShow("doulbeLine2", echartdata2));

		return echartShows;
	}

	/**
	 * 获取单Y轴图显示数据
	 * 
	 * @param fields
	 *            要显示的参数字段，其个数即要生成的图的个数
	 * @param legend
	 *            代表要显示对象的ID，例如单井数据则代表单井的ID，井组数据则代码井组的ID
	 * @param level
	 *            层级，代表是单井数据，井组数据，区间数据还是总油田数据
	 * @param chartStyle
	 *            图风格：叠合图
	 * @return
	 */
	public List<EchartShow> getLineChartData(List<String> fields, List<String> legend, String level,
			String chartStyle) {

		List<EchartShow> echartShows = new ArrayList<EchartShow>();

		/**
		 * 遍历fields，根据每个field生成一张图
		 */
		for (String fieldcode : fields) {

			// 获取参数信息
			String sql = "select * from dictionary t where t.type= 'chart' and t.fieldcode = '" + fieldcode + "'";
			List<Record> data = Db.use("mysql").find(sql);

			if (data.size() != 0) {

				Record record0 = data.get(0);
				String tablename = record0.getStr("tablename");// field对应表名
				String fieldname = record0.getStr("fieldname");// field对应属性名
				String color = record0.getStr("color");
				String unit = record0.getStr("unitname").trim().equals("") ? record0.getStr("unitname").trim()
						: "(" + record0.getStr("unitname").trim() + ")";// 单位

				String showname = fieldname + unit + " 折线图";// 图名

				List<String> xAxis = new ArrayList<String>(); // 横坐标

				List<Series> series = new ArrayList<Series>();// 纵坐标

				List<String> legendNames = new ArrayList<String>();// 折线名

				if (chartStyle.equals("stack")) {// 图风格为叠合图情况

					if (level.equals("-1")) { // 获取按投产时间的叠合图的横纵数据

						showname = "\n按投产时间叠合图";

						String[] timeArray = { "2010投产井", "2011投产井", "2012投产井" };
						legend = Arrays.asList(timeArray);
						for (String node : legend) {

							sql = "SELECT t1.`date`,tt2.`" + fieldcode
									+ "` FROM dateshow t1 LEFT JOIN (SELECT tt.`SCRQ`,SUM(tt." + fieldcode + ") AS "
									+ fieldcode + " FROM "
									+ " (SELECT t1.`wellcode`,t1.`TCRQ`,t2.`QQ`,t2.`SCRQ` FROM cjscjt t1," + tablename
									+ " t2 " + " WHERE t1.`wellcode` = t2.`wellcode` AND YEAR(t1.`TCRQ`)="
									+ node.substring(0, 4)
									+ ") AS tt GROUP BY tt.`SCRQ` ORDER BY tt.`SCRQ`) AS tt2 ON t1.`date` = tt2.`SCRQ`";

							getStackData("date", fieldcode, sql, xAxis, series, node);

						}

					} else if (level.equals("2")) {// 获取按井组的叠合图的横纵数据

						showname = "\n按井组叠合图";
						for (String node : legend) {

							sql = getWellGroupSql(legend, fieldcode, tablename, node);
							getStackData("date", fieldcode, sql, xAxis, series, node);

						}

					}

				} else if (chartStyle.equals("line")) {// 获取单井，井组，区间和总产气的横纵数据

					legendNames.clear();
					String legendName;

					if (level.equals("0")) { // 获取气田总产气的横纵数据

						sql = "SELECT t.SCRQ,SUM(t." + fieldcode + ") AS " + fieldcode + " FROM " + tablename
								+ " t GROUP BY t.SCRQ ORDER BY t.SCRQ";

						legendName = getLineData("SCRQ", fieldcode, sql, xAxis, series);
						legendNames.add(legendName);

					} else {
						for (String node : legend) {

							if (level.equals("3")) {// 获取单井情况的SQL语句
								sql = getSingleWellSql(legend, fieldcode, tablename, node);

							} else if (level.equals("2")) {// 获取井组情况的SQL语句
								sql = getWellGroupSql(legend, fieldcode, tablename, node);

							} else if (level.equals("1")) {// 获取区块情况的SQL语句
								sql = getWellBlockSql(legend, fieldcode, tablename, node);
							}

							legendName = getLineData("date", fieldcode, sql, xAxis, series);
							legendNames.add(legendName);

						}
					}
				}

				EchartLineData echartdata = new EchartLineData(legendNames, xAxis, series, showname, color);// 填充前台eChart需要的数据
				echartShows.add(new EchartShow(fieldcode, echartdata));
			}
		}

		return echartShows;
	}

	/**
	 * 获取归一化的单Y轴图显示数据
	 * 
	 * @param fields
	 *            要显示的参数字段，其个数即要生成的图的个数
	 * @param legend
	 *            代表要显示对象的ID，例如单井数据则代表单井的ID，井组数据则代码井组的ID
	 * @param level
	 *            层级，代表是单井数据，井组数据，区间数据还是总油田数据
	 * @param chartStyle
	 *            图风格：叠合图
	 * @param normalType
	 *            规划类型：0表示无归一，1表示生成时间归一，2表示产量归一，3表示全归一
	 * @return
	 */
	public List<EchartShow> getLineChartData(List<String> fields, List<String> legend, String level, String chartStyle,
			String normalType) {

		List<EchartShow> echartShows = new ArrayList<EchartShow>();

		/**
		 * 每个field生成一张图
		 */
		for (String fieldcode : fields) {

			String sql = "select * from dictionary t where t.type= 'chart' and t.fieldcode = '" + fieldcode + "'";
			List<Record> data = Db.use("mysql").find(sql);
			String xsql = null, ysql = null;

			if (data.size() != 0) {

				Record record0 = data.get(0);
				String tablename = record0.getStr("tablename");
				String fieldname = record0.getStr("fieldname");
				String color = record0.getStr("color");
				String unit = record0.getStr("unitname").trim().equals("") ? record0.getStr("unitname").trim()
						: "(" + record0.getStr("unitname").trim() + ")";

				String showname = fieldname + unit + " 折线图";

				List<String> xAxis = new ArrayList<String>(); // 横坐标

				List<Series> series = new ArrayList<Series>();// 纵坐标

				List<String> legendNames = new ArrayList<String>();// 每条折线代表的对象的对象名

				legendNames.clear();
				String legendName;

				if (normalType.equals("1")) {// 时间归一
					if (chartStyle.equals("line")) {// 获取单井，井组，区间和总产气的横纵归一数据

						if (level.equals("0")) { // 获取气田总产气的横纵归一数据

							xsql = "SELECT MAX(t.dayLength) as dayLength FROM (SELECT t2.`wellcode`,COUNT(t2.`wellcode`) AS dayLength FROM "
									+ tablename + " t2" + " GROUP BY t2.`wellcode` ORDER BY dayLength DESC) AS t";
							ysql = "SELECT t.SCRQ,SUM(t." + fieldcode + ") AS " + fieldcode + " FROM " + tablename
									+ " t GROUP BY t.SCRQ ORDER BY t.SCRQ";

							getxAxisNormalData(xAxis, xsql);
							legendName = getLineData("SCRQ", fieldcode, ysql, xAxis, series, normalType);
							legendNames.add(legendName);

						} else {
							for (String node : legend) {

								if (level.equals("3")) {
									xsql = getSingleWellXSql(legend, tablename);
									ysql = getSingleWellSql(legend, fieldcode, tablename, node, normalType);

								} else if (level.equals("2")) {
									xsql = getWellGroupXSql(legend, tablename);
									ysql = getWellGroupSql(legend, fieldcode, tablename, node, normalType);

								} else if (level.equals("1")) {
									xsql = getWellBlockXSql(legend, tablename);
									ysql = getWellBlockSql(legend, fieldcode, tablename, node, normalType);
								}

								getxAxisNormalData(xAxis, xsql);

								legendName = getLineData("date", fieldcode, ysql, xAxis, series, normalType);

								legendNames.add(legendName);

							}
						}
					}

				} else if (normalType.equals("2")) {// 产量归一

					for (String node : legend) {

						if (level.equals("3")) {

							ysql = getSingleWellSql(legend, fieldcode, tablename, node, normalType);

						}

						legendName = getLineData("date", fieldcode, ysql, xAxis, series, normalType);

						legendNames.add(legendName);

					}

				} else if (normalType.equals("3")) {// 全归一

					for (String node : legend) {

						if (level.equals("3")) {
							xsql = getSingleWellXSql(legend, tablename);
							ysql = getSingleWellSql(legend, fieldcode, tablename, node, normalType);

						}

						getxAxisNormalData(xAxis, xsql);

						legendName = getLineData("date", fieldcode, ysql, xAxis, series, normalType);

						legendNames.add(legendName);

					}

				}

				// EchartLineData echartdata = new EchartLineData(legend, xAxis,
				// series, showname);
				EchartLineData echartdata = new EchartLineData(legendNames, xAxis, series, showname, color);
				echartShows.add(new EchartShow(fieldcode, echartdata));
			}
		}

		return echartShows;
	}

	/**
	 * 获取归一化的X轴数据
	 * 
	 * @param xAxis
	 *            要填充的X轴数据列表
	 * @param xsql
	 */
	private void getxAxisNormalData(List<String> xAxis, String xsql) {

		System.out.println("xsql：" + xsql);
		Record record = Db.use("mysql").find(xsql).get(0);
		int dayLength = record.getLong("dayLength").intValue();

		// System.out.println("dayLength：" + dayLength);
		if (xAxis.size() == 0) {
			for (int i = 0; i < dayLength; i++) {
				xAxis.add((i + 1) + "天");
			}
		}

	}

	/**
	 * 获取双Y轴数据
	 * 
	 * @param sql
	 *            要执行的SQL语句
	 * @param yAxis
	 *            要显示的参数字段，其个数即要生成的图的个数
	 * @return
	 */
	private EchartLineData getDoubleLineData(String sql, String[][] yAxis, String showname) {

		List<String> xAxis = new ArrayList<String>(); // 横坐标

		List<Series> series = new ArrayList<Series>();// 纵坐标

		List<Record> data;
		data = Db.use("mysql").find(sql);

		List<BigDecimal> seriesData1 = new ArrayList<BigDecimal>();
		List<BigDecimal> seriesData2 = new ArrayList<BigDecimal>();

		for (Record record : data) {
			xAxis.add(record.getDate("SCRQ").toString());

			BigDecimal decimal1 = record.getBigDecimal(yAxis[0][0]);
			BigDecimal decimal2 = record.getBigDecimal(yAxis[0][1]);

			if (decimal1 == null) {
				decimal1 = new BigDecimal("0.0");
			}
			if (decimal2 == null) {
				decimal2 = new BigDecimal("0.0");
			}

			seriesData1.add(decimal1);
			seriesData2.add(decimal2);

		}

		series.add(new Series(yAxis[1][0], "line", seriesData1, 0));
		series.add(new Series(yAxis[1][1], "line", seriesData2, 1));

		return new EchartLineData(xAxis, series, showname);
	}

	/**
	 * 获取叠合图数据：根据xfieldcode获取xAxis数据并填充到xAxis列表，根据fieldcode获取属性数据并填充到Series列表中
	 * 
	 * @param xfieldcode
	 *            横坐标名
	 * @param fieldcode
	 *            参数编码，即纵坐标名
	 * @param sql
	 * @param xAxis
	 *            横坐标数据
	 * @param series
	 *            纵坐标数据
	 * @param node
	 *            折线代表的对象id：单井即为单井id，井组即为井组id
	 */
	private void getStackData(String xfieldcode, String fieldcode, String sql, List<String> xAxis, List<Series> series,
			String node) {

		System.out.println(sql);

		List<Record> data;
		data = Db.use("mysql").find(sql);

		List<BigDecimal> seriesData = new ArrayList<BigDecimal>();
		for (Record record : data) {
			if (xAxis.size() < data.size()) {
				xAxis.add(record.getDate(xfieldcode).toString());
			}
			BigDecimal decimal = record.getBigDecimal(fieldcode);
			if (decimal == null) {
				decimal = new BigDecimal("0.0");
			}
			seriesData.add(decimal);
		}

		itemStyle itemStyle = new itemStyle();
		series.add(new Series(node, "line", seriesData, itemStyle));
	}

	/**
	 * 获取折线图数据：根据xfieldcode获取xAxis数据并填充到xAxis列表，根据fieldcode获取属性数据并填充到Series列表中
	 * 
	 * @param xfieldcode
	 *            横坐标名
	 * @param fieldcode
	 *            参数编码，即纵坐标名
	 * @param sql
	 * @param xAxis
	 *            横坐标数据
	 * @param series
	 *            纵坐标数据
	 * @return
	 */
	private String getLineData(String xfieldcode, String fieldcode, String sql, List<String> xAxis,
			List<Series> series) {
		List<Record> data;
		data = Db.use("mysql").find(sql);

		String legendName = null;

		List<BigDecimal> seriesData = new ArrayList<BigDecimal>();
		for (Record record : data) {

			if (record.getStr("wellname") != null && legendName == null) {
				legendName = record.getStr("wellname");
			}

			if (xAxis.size() < data.size()) {
				xAxis.add(record.getDate(xfieldcode).toString());
			}
			BigDecimal decimal = record.getBigDecimal(fieldcode);
			if (decimal == null) {
				decimal = new BigDecimal("0.0");
			}
			seriesData.add(decimal);
		}

		if (legendName == null) {
			legendName = "气田总产气";
		}

		series.add(new Series(legendName, "line", seriesData));
		return legendName;
	}

	/**
	 * 获取归一化折线图数据：
	 * 1、先根据normalType决定是否需要对xAxis列表进行填充，如果需要则根据xfieldcode获取xAxis数据并填充到xAxis列表，
	 * 否则不对xAxis列表做处理 2、根据fieldcode获取属性数据并填充到Series列表中
	 * 
	 * @param xfieldcode
	 *            横坐标名
	 * @param fieldcode
	 *            参数编码，即纵坐标名
	 * @param sql
	 * @param xAxis
	 *            横坐标数据
	 * @param series
	 *            纵坐标数据
	 * @param normalType
	 *            规划类型：0表示无归一，1表示生成时间归一，2表示产量归一，3表示全归一
	 * @return
	 */
	private String getLineData(String xfieldcode, String fieldcode, String sql, List<String> xAxis, List<Series> series,
			String normalType) {
		List<Record> data;
		data = Db.use("mysql").find(sql);

		String legendName = null;

		List<BigDecimal> seriesData = new ArrayList<BigDecimal>();
		for (Record record : data) {

			if (record.getStr("wellname") != null && legendName == null) {
				legendName = record.getStr("wellname");
			}

			if (normalType.equals("2")) {// 如果只有产量归一则需要对X轴进行处理
				if (xAxis.size() < data.size()) {
					xAxis.add(record.getDate(xfieldcode).toString());
				}
			}

			BigDecimal decimal = record.getBigDecimal(fieldcode);
			if (decimal == null) {
				decimal = new BigDecimal("0.0");
			}
			seriesData.add(decimal);
		}

		if (legendName == null) {
			legendName = "气田总产气";
		}

		series.add(new Series(legendName, "line", seriesData));
		return legendName;
	}

	/**
	 * 获取非归一化的单井横纵轴数据
	 * 
	 * @param legend
	 * @param fieldcode
	 * @param tablename
	 * @param node
	 * @return
	 */
	private String getSingleWellSql(List<String> legend, String fieldcode, String tablename, String node) {
		String sql;
		StringBuffer getAllwellcode = new StringBuffer();
		for (int i = 0; i < legend.size(); i++) {
			if (i != legend.size() - 1)
				getAllwellcode.append("t2.wellcode = '" + legend.get(i) + "' or ");
			else
				getAllwellcode.append("t2.wellcode = '" + legend.get(i) + "' ");
		}

		sql = "SELECT tt1.date,tt2." + fieldcode
				+ ",tt2.wellname FROM (SELECT t.`date` FROM dateshow t , (SELECT MAX(t2.`SCRQ`) AS MAX,MIN( t2.`SCRQ`) AS MIN FROM "
				+ tablename + " t2" + " WHERE " + getAllwellcode
				+ ") AS tt WHERE t.date <= tt.max AND t.date >= tt.min) AS tt1 LEFT JOIN ("
				+ "SELECT t.wellname,t.wellcode,t.SCRQ,t." + fieldcode + " FROM " + tablename
				+ " t WHERE t.wellcode = '" + node + "' order by t.SCRQ" + ") AS tt2 ON tt1.date = tt2.scrq";
		// System.out.println(sql);

		return sql;
	}

	/**
	 * 生成获取归一化的Y轴数据的SQL
	 * 
	 * @param legend
	 *            代表要显示对象的ID列表，例如单井数据则代表单井的ID，井组数据则代码井组的ID
	 * @param fieldcode
	 *            要显示的参数字段的编码
	 * @param tablename
	 *            表名
	 * @param node
	 *            折线代表的对象id：单井即为单井id，井组即为井组id
	 * @param normalType
	 *            规划类型：0表示无归一，1表示生成时间归一，2表示产量归一，3表示全归一
	 * @return
	 */
	private String getSingleWellSql(List<String> legend, String fieldcode, String tablename, String node,
			String normalType) {
		String ysql = null;
		StringBuffer getAllWellCode = new StringBuffer();
		for (int i = 0; i < legend.size(); i++) {
			if (i != legend.size() - 1)
				getAllWellCode.append("t2.wellcode = '" + legend.get(i) + "' or ");
			else
				getAllWellCode.append("t2.wellcode = '" + legend.get(i) + "' ");
		}

		if (normalType.equals("1")) {// 只有时间归一的情况，X轴数据和Y轴数据单独获取，Y轴数据不进行处理
			ysql = "SELECT t2.`" + fieldcode + "`,t2.wellname FROM " + tablename + " t2 WHERE t2.wellcode= '" + node
					+ "' order by t2.SCRQ";

		} else if (normalType.equals("2")) {// 只有产量归一的情况，需要同时获取X轴和Y轴取的数据，并对Y轴数据进行处理

			ysql = "SELECT tt1.date,CONVERT(tt2.`" + fieldcode + "`*24/tt2.`SCSJ`,DECIMAL(10,2)) AS '" + fieldcode
					+ "',tt2.wellname FROM (SELECT t.`date` FROM dateshow t , (SELECT MAX(t2.`SCRQ`) AS MAX,MIN( t2.`SCRQ`) AS MIN FROM "
					+ tablename + " t2" + " WHERE " + getAllWellCode
					+ ") AS tt WHERE t.date <= tt.max AND t.date >= tt.min) AS tt1 LEFT JOIN ("
					+ "SELECT t.wellname,t.wellcode,t.SCRQ,t.SCSJ,t." + fieldcode + " FROM " + tablename
					+ " t WHERE t.wellcode = '" + node + "' order by t.SCRQ" + ") AS tt2 ON tt1.date = tt2.scrq";

		} else {// 全归一情况，X轴数据和Y轴数据单独获取，并对Y轴数据进行处理
			ysql = "SELECT t2.wellname,CONVERT(t2.`" + fieldcode + "`*24/t2.`SCSJ`,DECIMAL(10,2)) AS '" + fieldcode
					+ "' FROM " + tablename + " t2 WHERE t2.wellcode= '" + node + "' order by t2.SCRQ";
		}
		System.out.println("ysql：" + ysql);
		return ysql;
	}

	/**
	 * 生成获取单井归一化的X轴数据的SQL
	 * 
	 * @param legend
	 *            代表要显示对象的ID，例如单井数据则代表单井的ID，井组数据则代码井组的ID
	 * @param tablename
	 *            表名
	 * @return
	 */
	private String getSingleWellXSql(List<String> legend, String tablename) {
		String sql;
		StringBuffer getAllWellCode = new StringBuffer();
		for (int i = 0; i < legend.size(); i++) {
			if (i != legend.size() - 1)
				getAllWellCode.append("t2.wellcode = '" + legend.get(i) + "' or ");
			else
				getAllWellCode.append("t2.wellcode = '" + legend.get(i) + "' ");
		}

		sql = "SELECT MAX(t.dayLength) as dayLength FROM (SELECT t2.`wellcode`,COUNT(t2.`wellcode`) AS dayLength FROM "
				+ tablename + " t2 WHERE " + getAllWellCode + " GROUP BY t2.`wellcode` ORDER BY dayLength DESC) AS t";

		// System.out.println(sql);
		return sql;
	}

	/**
	 * 生成非归一化的井组横纵轴数据
	 * 
	 * @param legend
	 * @param fieldcode
	 * @param tablename
	 * @param node
	 * @return
	 */
	private String getWellGroupSql(List<String> legend, String fieldcode, String tablename, String node) {

		tablename = "jzhz";
		String sql;
		StringBuffer getAllwellname = new StringBuffer();
		for (int i = 0; i < legend.size(); i++) {
			if (i != legend.size() - 1)
				getAllwellname.append("t2.wellgroupcode = '" + legend.get(i) + "' or ");
			else
				getAllwellname.append("t2.wellgroupcode = '" + legend.get(i) + "' ");
		}

		sql = "SELECT tt1.date,tt2.wellgroupname AS wellname,tt2." + fieldcode
				+ " FROM(SELECT t.`date` FROM dateshow t,"
				+ "(SELECT MAX(t2.`SCRQ`) AS MAX,MIN( t2.`SCRQ`) AS MIN FROM " + tablename + " t2 WHERE "
				+ getAllwellname + " ) AS tt WHERE t.date <= tt.max AND t.date >= tt.min) AS tt1 "
				+ "LEFT JOIN (SELECT t1.`wellgroupname`,t1.SCRQ,SUM(t1.`" + fieldcode + "`) AS " + fieldcode + " FROM "
				+ tablename + " t1 " + "WHERE t1.wellgroupcode = '" + node
				+ "' GROUP BY  t1.SCRQ ORDER BY t1.SCRQ) AS tt2 ON tt1.date = tt2.scrq";

		// System.out.println(sql);

		return sql;
	}

	/**
	 * 生成获取井组归一化的X轴数据的SQL
	 * 
	 * @param legend
	 * @param tablename
	 * @return
	 */
	private String getWellGroupXSql(List<String> legend, String tablename) {
		tablename = "jzhz";
		String sql;
		StringBuffer getAllWellCode = new StringBuffer();
		for (int i = 0; i < legend.size(); i++) {
			if (i != legend.size() - 1)
				getAllWellCode.append("t2.wellgroupcode = '" + legend.get(i) + "' or ");
			else
				getAllWellCode.append("t2.wellgroupcode = '" + legend.get(i) + "' ");
		}

		sql = "SELECT MAX(t.dayLength) as dayLength FROM (SELECT t2.`wellgroupcode`,COUNT(t2.`wellgroupcode`) AS dayLength FROM "
				+ tablename + " t2 WHERE " + getAllWellCode
				+ " GROUP BY t2.`wellgroupcode` ORDER BY dayLength DESC) AS t";

		// System.out.println(sql);
		return sql;
	}

	/**
	 * 生成获取井组归一化的Y轴数据的SQL
	 * 
	 * @param legend
	 *            代表要显示对象的ID列表，例如单井数据则代表单井的ID，井组数据则代码井组的ID
	 * @param fieldcode
	 *            要显示的参数字段的编码
	 * @param tablename
	 *            表名
	 * @param node
	 *            折线代表的对象id：单井即为单井id，井组即为井组id
	 * @param normalType
	 *            规划类型：0表示无归一，1表示生成时间归一，2表示产量归一，3表示全归一
	 * @return
	 */
	private String getWellGroupSql(List<String> legend, String fieldcode, String tablename, String node,
			String normalType) {
		tablename = "jzhz";
		String ysql = null;
		StringBuffer getAllWellCode = new StringBuffer();
		for (int i = 0; i < legend.size(); i++) {
			if (i != legend.size() - 1)
				getAllWellCode.append("t2.wellgroupcode = '" + legend.get(i) + "' or ");
			else
				getAllWellCode.append("t2.wellgroupcode = '" + legend.get(i) + "' ");
		}

		if (normalType.equals("1")) {// 只有时间归一的情况，X轴数据和Y轴数据单独获取，Y轴数据不进行处理
			ysql = "SELECT t2.`" + fieldcode + "`,t2.wellgroupname as wellname FROM " + tablename
					+ " t2 WHERE t2.wellgroupcode= '" + node + "' order by t2.SCRQ";
		}
		System.out.println("ysql：" + ysql);
		return ysql;
	}

	/**
	 * 生成非归一化的区块横纵轴数据
	 * 
	 * @param legend
	 * @param fieldcode
	 * @param tablename
	 * @param node
	 * @return
	 */
	private String getWellBlockSql(List<String> legend, String fieldcode, String tablename, String node) {

		tablename = "qkhz";
		String sql;
		StringBuffer getAllwellname = new StringBuffer();
		for (int i = 0; i < legend.size(); i++) {
			if (i != legend.size() - 1)
				getAllwellname.append("t2.blockcode = '" + legend.get(i) + "' or ");
			else
				getAllwellname.append("t2.blockcode = '" + legend.get(i) + "' ");
		}

		sql = "SELECT tt1.date,tt2.blockname AS wellname,tt2." + fieldcode + " FROM(SELECT t.`date` FROM dateshow t,"
				+ "(SELECT MAX(t2.`SCRQ`) AS MAX,MIN( t2.`SCRQ`) AS MIN FROM " + tablename + " t2 WHERE "
				+ getAllwellname + " ) AS tt WHERE t.date <= tt.max AND t.date >= tt.min) AS tt1 "
				+ "LEFT JOIN (SELECT t1.`blockname`,t1.SCRQ,SUM(t1.`" + fieldcode + "`) AS " + fieldcode + " FROM "
				+ tablename + " t1 " + "WHERE t1.blockcode = '" + node
				+ "' GROUP BY  t1.SCRQ ORDER BY t1.SCRQ) AS tt2 ON tt1.date = tt2.scrq";
		// System.out.println(sql);

		return sql;
	}

	/**
	 * 生成获取区块归一化的X轴数据的SQL
	 * 
	 * @param legend
	 * @param tablename
	 * @return
	 */
	private String getWellBlockXSql(List<String> legend, String tablename) {
		tablename = "qkhz";
		String sql;
		StringBuffer getAllWellCode = new StringBuffer();
		for (int i = 0; i < legend.size(); i++) {
			if (i != legend.size() - 1)
				getAllWellCode.append("t2.blockcode = '" + legend.get(i) + "' or ");
			else
				getAllWellCode.append("t2.blockcode = '" + legend.get(i) + "' ");
		}

		sql = "SELECT MAX(t.dayLength) as dayLength FROM (SELECT t2.`blockcode`,COUNT(t2.`blockcode`) AS dayLength FROM "
				+ tablename + " t2 WHERE " + getAllWellCode + " GROUP BY t2.`blockcode` ORDER BY dayLength DESC) AS t";

		// System.out.println(sql);
		return sql;
	}

	/**
	 * 生成获取区块归一化的Y轴数据的SQL
	 * 
	 * @param legend
	 *            代表要显示对象的ID列表，例如单井数据则代表单井的ID，井组数据则代码井组的ID
	 * @param fieldcode
	 *            要显示的参数字段的编码
	 * @param tablename
	 *            表名
	 * @param node
	 *            折线代表的对象id：单井即为单井id，井组即为井组id
	 * @param normalType
	 *            规划类型：0表示无归一，1表示生成时间归一，2表示产量归一，3表示全归一
	 * @return
	 */
	private String getWellBlockSql(List<String> legend, String fieldcode, String tablename, String node,
			String normalType) {
		tablename = "qkhz";
		String ysql = null;
		StringBuffer getAllWellCode = new StringBuffer();

		if (normalType.equals("1")) {// 只有时间归一的情况，X轴数据和Y轴数据单独获取，Y轴数据不进行处理
			ysql = "SELECT t2.`" + fieldcode + "`,t2.blockname as wellname FROM " + tablename
					+ " t2 WHERE t2.blockcode= '" + node + "' order by t2.SCRQ";
		}
		System.out.println("ysql：" + ysql);
		return ysql;
	}

}